SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.[DISC_DefaultStampConfigList]
	@DistributorID varchar(50),
	@DRMType varchar(20),
	@SortExpression varchar(100),
	@MaximumRows int = null, 
	@StartRowIndex int = null
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQLString NVARCHAR(1000)
	DECLARE @ParmDefinition NVARCHAR(100)

	/* Build the SQL string once.*/
	IF (@DRMType IS NULL)
		SET @SQLString = N'WITH ItemRN AS (SELECT ROW_NUMBER() OVER(ORDER BY '+ @SortExpression + ') AS RowNum, [ID], [Name], [Description], [DRMType] FROM (
			SELECT [ID], [Name], [Description], ''Stamp'' AS [DRMType] FROM [DISC_DefaultStampConfig] WHERE [distID] = @DistributorID
			UNION ALL
			SELECT [ID], [Name], [Description], [DRMType] FROM [DISC_DefaultDRMStampConfig] WHERE [distID] = @DistributorID) AS [DefaultStampConfig])'
	ELSE IF (@DRMType = 'Stamp')
		SET @SQLString = N'WITH ItemRN AS (SELECT ROW_NUMBER() OVER(ORDER BY '+ @SortExpression + ') AS RowNum, [ID], [Name], [Description], ''Stamp'' AS [DRMType] FROM [DISC_DefaultStampConfig] WHERE [distID] = @DistributorID)'
	ELSE
		SET @SQLString = N'WITH ItemRN AS (SELECT ROW_NUMBER() OVER(ORDER BY '+ @SortExpression + ') AS RowNum, [ID], [Name], [Description], [DRMType] FROM [DISC_DefaultDRMStampConfig] WHERE [distID] = @DistributorID AND [DRMType] = @DRMType)'

	SET @SQLString = @SQLString + N' SELECT [ID], [Name], [Description], [DRMType] FROM ItemRN WHERE RowNum BETWEEN @startRowIndex+1 AND (@startRowIndex+@maximumRows);'
	SET @ParmDefinition = N'@MaximumRows int, @StartRowIndex int, @DistributorID varchar(50), @DRMType varchar(20)'
	EXECUTE sp_executesql @SQLString, @ParmDefinition, @MaximumRows, @StartRowIndex, @DistributorID, @DRMType
END
GO
